package com.qingke.jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Properties;
import java.util.Scanner;




public class JdbcConntest {

	public static void main(String[] args) {
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
	
			loadDriverClass();
			
			try {
				conn=getConnection();
				stat=conn.createStatement();
				rs = stat.executeQuery("select * from player");
				while (rs.next()) {
					String id = rs.getString("id");
					String name = rs.getString("name");
					int score = rs.getInt("score");
					System.out.println("player: id =" + id + ",name=" + name + ",score=" + score);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				cleanUp(conn, stat, rs);
			}
			PreparedStatement ps =null;
			conn =getConnection();
			try {
				ps=conn.prepareStatement("select * from player where id=?");
				ps.setInt(1, 2);
				rs=ps.executeQuery();
				while (rs.next()) {
					String id = rs.getString("id");
					String name = rs.getString("name");
					int score = rs.getInt("score");
					System.out.println("player: id =" + id + ",name=" + name + ",score=" + score);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				cleanUp(conn, ps, rs);
			}
//			Scanner sc =new Scanner(System.in);
//			System.out.println("username:");
//			String username =sc.nextLine();
//			System.out.println("password:");
//			String password = sc.nextLine();
//			try {
//				stat =conn.createStatement();
//				String sql ="select * from customer where username='"+username+"'and password = '"+password+"'";
//				System.out.println(sql);
//				rs=stat.executeQuery(sql);
//				while (rs.next()) {
//					String id = rs.getString("id");
//					String name = rs.getString("username");
//					String phone = rs.getString("phone");
//					System.out.println("customer: id =" + id + ",name=" + name + ",phone=" + phone);
//
//				}
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}finally{
//				cleanUp(conn, stat, rs);
//			}
			
			conn=getConnection();
			Scanner sc =new Scanner(System.in);
			System.out.println("enter username pattern: ");
			String pattern =sc.nextLine();
			CallableStatement cs=null;
			try {
				cs=conn.prepareCall("call qingke.get_player(?,?)");
				cs.setString(1, pattern);
				cs.registerOutParameter(2, Types.INTEGER);
				cs.execute();
				int resultcount =cs.getInt(2);
				System.out.println("count="+resultcount);
				rs=cs.getResultSet();
				while (rs.next()) {
					String id = rs.getString("id");
					String name = rs.getString("name");
					int score = rs.getInt("score");
					System.out.println("player: id =" + id + ",name=" + name + ",score=" + score);

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				cleanUp(conn, cs, rs);
				sc.close();
			}
			
		
		

	}
	protected static void loadDriverClass(){
		try {
			Class.forName(getProperties("db.driver"));
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	protected static Connection getConnection(){
		Connection conn = null;
		String url = getProperties("db.url");
		String u = getProperties("db.username");
		String p = getProperties("db.password");
		try {
			conn = DriverManager.getConnection(url, u, p);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;

	}
	protected static void cleanUp(Connection conn,Statement stat,ResultSet rs){
		
		try {
			if (rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	
	}

	protected static String getProperties(String key) {
		Properties prop = new Properties();
		try {
			prop.load(new FileInputStream("jdbc.properties"));
			return prop.getProperty(key);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}

}
